﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Net.Mime;
using System.Text;
using Common.Constants;
using Common.DataModel;

namespace Common.Repository
{
    public class SqLiteDatabase : BaseSqLiteDatabase
    {
        private const string LanguagesTableName = "SurveyLanguage";
        private const string TextAutomationTableName = "TextAutomation";

        #region Languages

        public ICollection<Language> GetLanguages()
        {
            var sql = string.Format("Select * from {0}", LanguagesTableName);
            var listLang = new List<Language>();
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var reader = mycommand.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                var lang = new Language { Id = reader.GetInt32(0), LanguageName = reader.GetString(1) };
                                listLang.Add(lang);
                            }
                        }
                        
                        reader.Close();
                        cnn.Close();
                    }

                }
            }
            catch (Exception e)
            {
                return null;
            }
            return listLang;
        }

        public Language GetLanguageById(int id)
        {
            var sql = string.Format("Select {0}, {1} from {2} where Id={3}", Constant.LanguageIdFieldName,
                Constant.LanguageNameFieldName, LanguagesTableName, id);
            
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var reader = mycommand.ExecuteReader();
                        if(reader != null)
                        {
                            var language = new Language();
                            while (reader.Read())
                            {   
                                language.Id = reader.GetInt32(0);
                                language.LanguageName = reader.GetString(1);
                            }

                            return language;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return null;
        }

        public Language GetLanguageByName(string name)
        {
            var sql = string.Format("Select Id, LanguageName from {0} where LanguageName like '{1}'", LanguagesTableName, name);

            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var reader = mycommand.ExecuteReader();
                        if (reader != null)
                        {
                            var language = new Language();
                            while (reader.Read())
                            {
                                language.Id = reader.GetInt32(0);
                                language.LanguageName = reader.GetString(1);
                            }

                            return language;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return null;
        }

        public bool IsNameExisted(int id, string name)
        {
            var sql = id == -1 ? string.Format("Select Id from {0} where LanguageName like '{1}'", LanguagesTableName, name) : 
                string.Format("Select Id from {0} where Id <> {1} and LanguageName like '{2}'", LanguagesTableName, id, name);

            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var existFlag = mycommand.ExecuteScalar();

                        return existFlag != null;
                    }
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        private int GetNewLanguageId()
        {
            var sql = string.Format("SELECT MAX(Id) + 1  FROM {0}", LanguagesTableName);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var newId = mycommand.ExecuteScalar() ?? 1;

                        return int.Parse(newId.ToString());
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public int InsertLanguage(string langName)
        {
            if (IsNameExisted(-1, langName)) return -1;

            var newId = GetNewLanguageId();
            if (newId == -1) return -2;

            var sql = string.Format("insert into {0}(id, LanguageName) values({1}, '{2}')", LanguagesTableName, newId, langName);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public int UpdateLanguage(int id, string langName)
        {
            if (IsNameExisted(id, langName)) return -1;

            var sql = string.Format("update {0} set LanguageName = '{1}' where id={2}", LanguagesTableName, langName, id);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public int DeleteLanguage(int id)
        {
            if (IsLanguageExisted(id)) return -1;

            var sql = string.Format("Delete from {0} where id={1}", LanguagesTableName, id);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        #endregion

        #region textautomation

        public bool IsLanguageExisted(int languageId)
        {
            var sql = string.Format("Select Id from {0} where LanguageId={1}", TextAutomationTableName, languageId);

            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var existFlag = mycommand.ExecuteScalar();

                        return existFlag != null;
                    }
                }
            }
            catch (Exception e)
            {
                return false;
            }
        }

        public ICollection<TextAutomation> GetTextAutomations()
        {
            var sql = string.Format("select {0}, {1}, {2}  from {3}", Constant.TextAutoIdFieldName, 
                Constant.TextAutoLangIdFieldName, Constant.TextAutoLiteralFieldName, TextAutomationTableName);

            var listTextAuto = new List<TextAutomation>();
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var reader = mycommand.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                var id = reader.GetInt32(0);
                                var langId = reader.GetInt32(1);
                                var literal = reader.GetString(2);

                                var lang = GetLanguageById(langId);
                                if(lang == null) return null;

                                var textAuto = new TextAutomation {Id = id, Language = lang, Literal = literal};
                                listTextAuto.Add(textAuto);
                            }
                        }

                        reader.Close();
                        cnn.Close();
                    }

                }
            }
            catch (Exception e)
            {
                return null;
            }
            return listTextAuto;
        }

        public ICollection<TextAutomation> GetTextAutomationsByLanguage(int languageId)
        {
            var sql = string.Format("select {0}, {1}, {2}  from {3} where {4}={5}", Constant.TextAutoIdFieldName,
                Constant.TextAutoLangIdFieldName, Constant.TextAutoLiteralFieldName, TextAutomationTableName,
                Constant.TextAutoLangIdFieldName, languageId);

            var listTextAuto = new List<TextAutomation>();
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var reader = mycommand.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                var id = reader.GetInt32(0);
                                var langId = reader.GetInt32(1);
                                var literal = reader.GetString(2);

                                var lang = GetLanguageById(langId);
                                if (lang == null) return null;

                                var textAuto = new TextAutomation { Id = id, Language = lang, Literal = literal };
                                listTextAuto.Add(textAuto);
                            }
                        }

                        reader.Close();
                        cnn.Close();
                    }

                }
            }
            catch (Exception e)
            {
                return null;
            }
            return listTextAuto;
        }

        public int InsertTextAutomation(int langId, string literal)
        {
            var newId = GetNewTextAutoId();
            if (newId == -1) return -2;

            var sql = string.Format("insert into {0}({1}, {2}, {3}) values({4}, {5}, '{6}')", 
                TextAutomationTableName, Constant.TextAutoIdFieldName, Constant.TextAutoLangIdFieldName, Constant.TextAutoLiteralFieldName,
                newId, langId, literal);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public int UpdateTextAutomation(int id, string literal)
        {
            var sql = string.Format("update {0} set {1} = '{2}' where id={3}", TextAutomationTableName,
                Constant.TextAutoLiteralFieldName, literal, id);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public int DeleteTextAutomation(int id)
        {
            var sql = string.Format("Delete from {0} where id={1}", TextAutomationTableName, id);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        return mycommand.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        private int GetNewTextAutoId()
        {
            var sql = string.Format("SELECT MAX({0}) + 1  FROM {1}", Constant.TextAutoIdFieldName, TextAutomationTableName);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;
                        var newId = mycommand.ExecuteScalar() ?? "1";

                        if (string.IsNullOrEmpty(newId.ToString())) newId = "1";

                        return int.Parse(newId.ToString());
                    }
                }
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public TextAutomation GetRandomTextAutomation(int langId)
        {
            var sql = string.Format("SELECT Id, Literal  FROM {0} ORDER BY RANDOM() Limit 1", TextAutomationTableName);
            try
            {
                using (var cnn = new SQLiteConnection(dbConnection))
                {
                    cnn.Open();

                    using (var mycommand = new SQLiteCommand(cnn))
                    {
                        mycommand.CommandText = sql;

                        var reader = mycommand.ExecuteReader();

                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                var id = reader.GetInt32(0);
                                var literal = reader.GetString(1);

                                return new TextAutomation { Id = id, Language = null, Literal = literal };
                            }
                        }
                        return null;
                    }
                }
            }
            catch (Exception e)
            {
                return null;
            }
        }

        #endregion
    }
}
